﻿Imports System.Data.OleDb
Imports System.Drawing

Public Class AddRecipe
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        'Parse the URL to get the value in the Querystring
        AddRecTitle.Text = "Add Recipe"
        Dim getQueryString As String = Request.QueryString("Type")

        'Set the label for the page title depending on the querystring value
        If getQueryString = "" Then
            AddRecTitle.Text = "Add Recipe"
        Else
            AddRecTitle.Text = "Edit Recipe"
        End If

        If Not IsPostBack Then
            'Check if the Gallery Table has any temporary files and delete them
            Dim delGalleryDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("recipeConnectionString").ConnectionString)
            Dim delGallerySqlString = "DELETE from GALLERY where GALLERY_TEMP=@f1 "
            Dim delGalleryCommand As OleDbCommand = New OleDbCommand(delGallerySqlString, delGalleryDbConn)
            delGalleryCommand.CommandType = CommandType.Text
            delGalleryCommand.Parameters.AddWithValue("@f1", True)
            delGalleryDbConn.Open()
            delGalleryCommand.ExecuteNonQuery()
            delGalleryCommand.Dispose()
            delGalleryDbConn.Close()
            delGalleryDbConn.Dispose()

            'Check if the Ingredients Table has any temporary files an delete them
            Dim delIngDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("recipeConnectionString").ConnectionString)
            Dim delIngDbString = "DELETE from INGREDIENT where ING_TEMP=@f1 "
            Dim delIngCommand As OleDbCommand = New OleDbCommand(delIngDbString, delIngDbConn)
            delIngCommand.CommandType = CommandType.Text
            delIngCommand.Parameters.AddWithValue("@f1", True)
            delIngDbConn.Open()
            delIngCommand.ExecuteNonQuery()
            delIngCommand.Dispose()
            delIngDbConn.Close()
            delIngDbConn.Dispose()

            'Check if the Methods Table has any temporary files an delete them
            Dim delMethDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("recipeConnectionString").ConnectionString)
            Dim delMethDbString = "DELETE from METHOD where METHOD_TEMP=@f1 "
            Dim delMethCommand As OleDbCommand = New OleDbCommand(delMethDbString, delMethDbConn)
            delMethCommand.CommandType = CommandType.Text
            delMethCommand.Parameters.AddWithValue("@f1", True)
            delMethDbConn.Open()
            delMethCommand.ExecuteNonQuery()
            delMethCommand.Dispose()
            delMethDbConn.Close()
            delMethDbConn.Dispose()

        End If

        If IsPostBack = False Then

            'Populating the Gallery Gridview
            Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("recipeConnectionString").ConnectionString)
            Dim SqlString As String = "SELECT * FROM GALLERY WHERE GALLERY_TEMP=@f1"
            Dim gCmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
            gCmd.CommandType = CommandType.Text
            gCmd.Parameters.AddWithValue("@f1", True)
            oleDbConn.Open()
            Dim gDataReader = gCmd.ExecuteReader()
            grd_Gallery.DataSource = gDataReader
            grd_Gallery.DataBind()

            'Populating the Ingredients Gridview
            Dim ingDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("recipeConnectionString").ConnectionString)
            Dim ingSqlString As String = "SELECT * FROM GALLERY WHERE GALLERY_TEMP=@f1"
            Dim ingCmd As OleDbCommand = New OleDbCommand(ingSqlString, ingDbConn)
            ingCmd.CommandType = CommandType.Text
            ingCmd.Parameters.AddWithValue("@f1", True)
            ingDbConn.Open()
            Dim ingDataReader = ingCmd.ExecuteReader()
            grd_Ingredients.DataSource = ingDataReader
            grd_Ingredients.DataBind()

            'Populating the Methods Gridview
            Dim methDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("recipeConnectionString").ConnectionString)
            Dim methSqlString As String = "SELECT * FROM METHOD WHERE METHOD_TEMP=@f1"
            Dim methCmd As OleDbCommand = New OleDbCommand(methSqlString, methDbConn)
            methCmd.CommandType = CommandType.Text
            methCmd.Parameters.AddWithValue("@f1", True)
            methDbConn.Open()
            Dim methDataReader = methCmd.ExecuteReader()
            grd_Method.DataSource = methDataReader
            grd_Method.DataBind()

        End If

    End Sub

    Protected Sub btn_AddRecipe_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_AddRecipe.Click

        Dim d As DateTime = Now
        Dim userUploadingID As Integer

        Dim myGUID As New System.Guid
        myGUID = Guid.NewGuid()
        Dim newFileName = myGUID.ToString() + ".jpg"

        'Load current data from the Profile table
        Dim profileDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("recipeConnectionString").ConnectionString)
        Dim profileSqlString As String = "SELECT * from PROFILE WHERE USER_UNAME=@f1"
        Dim pcmd As OleDbCommand = New OleDbCommand(profileSqlString, profileDbConn)
        pcmd.CommandType = CommandType.Text
        pcmd.Parameters.AddWithValue("@f1", User.Identity.Name)
        profileDbConn.Open()

        'Since we need to get back a record - establish data reader
        Dim pdr As OleDbDataReader = pcmd.ExecuteReader
        If pdr.HasRows Then
            pdr.Read()
            'Store the ID of the profile into userUploadingID
            If Not IsDBNull(pdr("USER_UNAME")) Then userUploadingID = pdr("PROFILE_ID")
        End If

        Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("recipeConnectionString").ConnectionString)
        Dim SqlString As String = "INSERT into RECIPE(REC_NAME,TYPE_ID,REC_STARS,REC_SERVES,REC_TIME,REC_DESC,REC_VIDURL,REC_DATE,USER_ID,REC_IMAGE,REC_STARSTIMES) Values (@f1,@f2,@f3,@f4,@f5,@f6,@f7,@f8,@f9,f10,f11)"
        Dim cmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", tb_RecipeName.Text)
        cmd.Parameters.AddWithValue("@f2", ddl_RecipeType.SelectedValue)
        cmd.Parameters.AddWithValue("@f3", rbl_RecipeStars.SelectedValue)
        cmd.Parameters.AddWithValue("@f4", tb_RecipeServes.Text)
        cmd.Parameters.AddWithValue("@f5", tb_RecipeTime.Text)
        cmd.Parameters.AddWithValue("@f6", tb_RecipeDescription.Text)
        cmd.Parameters.AddWithValue("@f7", "") 'Video URL not used anymore
        cmd.Parameters.AddWithValue("@f8", d.ToString("yyyy-MM-dd")) 'Today's date
        cmd.Parameters.AddWithValue("@f9", userUploadingID)
        cmd.Parameters.AddWithValue("@f10", "ui/noImage.jpg") 'Default image
        cmd.Parameters.AddWithValue("@f11", "1")

        oleDbConn.Open()
        cmd.ExecuteNonQuery()

        'Getting ID of the recipe just added so that it will be used to populate REC_ID in RECIPE_TAG table
        Dim newcmd As New OleDbCommand("SELECT @@IDENTITY", oleDbConn)
        Dim uid As Integer
        uid = newcmd.ExecuteScalar

        'Saving the rating into the Rating table so that each recipe that has been added has at least 1 rating - that of its uploader
        Dim RatOleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("recipeConnectionString").ConnectionString)
        Dim RatSqlString As String = "INSERT into RATING(RATING_VALUE,RECIPE_ID,USER_ID,RATING_DATE) Values (@f1,@f2,@f3,@f4)"
        Dim RatCmd As OleDbCommand = New OleDbCommand(RatSqlString, RatOleDbConn)
        RatCmd.CommandType = CommandType.Text
        RatCmd.Parameters.AddWithValue("@f1", rbl_RecipeStars.SelectedValue)
        RatCmd.Parameters.AddWithValue("@f2", uid)
        RatCmd.Parameters.AddWithValue("@f3", userUploadingID)
        RatCmd.Parameters.AddWithValue("@f4", d.ToString("yyyy-MM-dd"))
        RatOleDbConn.Open()
        RatCmd.ExecuteNonQuery()
        RatOleDbConn.Close()
        RatOleDbConn.Dispose()

        Dim oleDbConnTag As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("recipeConnectionString").ConnectionString)
        Dim SqlStringTag As String = "INSERT into RECIPE_TAG(REC_ID,TAG_ID) Values (@f1,@f2)"
        oleDbConnTag.Open()
        For Each cb As ListItem In chk_RecipeTag.Items
            If cb.Selected Then
                'here is where the join to the table of tags and recipes is happening
                Dim cmdTag As OleDbCommand = New OleDbCommand(SqlStringTag, oleDbConnTag)
                cmdTag.CommandType = CommandType.Text
                'going through all checkboxes and associating the value behind the checkbox with the value of the recipe
                cmdTag.Parameters.AddWithValue("@f1", uid)
                cmdTag.Parameters.AddWithValue("@f2", cb.Value)
                cmdTag.ExecuteNonQuery()
            End If
        Next

        'Associating the Recipe_ID with the images uploaded
        Dim goleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("recipeConnectionString").ConnectionString)
        Dim gSqlString = "UPDATE GALLERY SET RECIPE_ID=@f1,GALLERY_TEMP=@f2 WHERE GALLERY_TEMP=@f3"
        Dim gcommand As OleDbCommand = New OleDbCommand(gSqlString, goleDbConn)
        gcommand.CommandType = CommandType.Text
        gcommand.Parameters.AddWithValue("@f1", uid)
        gcommand.Parameters.AddWithValue("@f2", False)
        gcommand.Parameters.AddWithValue("@f3", True)
        goleDbConn.Open()
        gcommand.ExecuteNonQuery()

        'Find if an image is selected as the main image (from gridview) and if yes, get its id
        Dim row As GridViewRow = grd_Gallery.SelectedRow
        Dim recPhotoPath As String = ""
        Dim recID As String = ""

        If Not row Is Nothing Then
            Dim roleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("recipeConnectionString").ConnectionString)
            Dim rSqlString = "UPDATE GALLERY SET GALLERY_MAIN=@f1 WHERE GALLERY_ID=@f2"
            Dim rcommand As OleDbCommand = New OleDbCommand(rSqlString, roleDbConn)
            rcommand.CommandType = CommandType.Text
            rcommand.Parameters.AddWithValue("@f1", True)
            rcommand.Parameters.AddWithValue("@f2", row.Cells(1).Text)
            roleDbConn.Open()
            rcommand.ExecuteNonQuery()

            'Read the path of the selected row from the Gallery ...
            Dim pathOleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("recipeConnectionString").ConnectionString)
            Dim pathSqlString As String = "SELECT * from GALLERY WHERE GALLERY_ID=@f1"
            Dim pathCmd As OleDbCommand = New OleDbCommand(pathSqlString, pathOleDbConn)
            pathCmd.CommandType = CommandType.Text
            pathCmd.Parameters.AddWithValue("@f1", row.Cells(1).Text)
            pathOleDbConn.Open()
            Dim pathDr As OleDbDataReader = pathCmd.ExecuteReader
            If pathDr.HasRows Then
                pathDr.Read()
                If Not IsDBNull(pathDr("GALLERY_PATH")) Then recPhotoPath = pathDr("GALLERY_PATH")
                If Not IsDBNull(pathDr("RECIPE_ID")) Then recID = pathDr("RECIPE_ID")
            End If

        End If

        'Save the path of the main image into the recipe table (as the main image)
        Dim recoleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("recipeConnectionString").ConnectionString)
        Dim recSqlString = "UPDATE RECIPE SET REC_IMAGE=@f1 WHERE REC_ID=@f2"
        Dim recCommand As OleDbCommand = New OleDbCommand(recSqlString, recoleDbConn)
        recCommand.CommandType = CommandType.Text
        recCommand.Parameters.AddWithValue("@f1", recPhotoPath)
        recCommand.Parameters.AddWithValue("@f2", recID)
        recoleDbConn.Open()
        recCommand.ExecuteNonQuery()

        'Associating the Recipe_ID with the ingredients uploaded
        Dim ioleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("recipeConnectionString").ConnectionString)
        Dim iSqlString = "UPDATE INGREDIENT SET REC_ID=@f1,ING_TEMP=@f2 WHERE ING_TEMP=@f3"
        Dim icommand As OleDbCommand = New OleDbCommand(iSqlString, ioleDbConn)
        icommand.CommandType = CommandType.Text
        icommand.Parameters.AddWithValue("@f1", uid)
        icommand.Parameters.AddWithValue("@f2", False)
        icommand.Parameters.AddWithValue("@f3", True)
        ioleDbConn.Open()
        icommand.ExecuteNonQuery()

        'Associating the Recipe_ID with the methods uploaded
        Dim moleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("recipeConnectionString").ConnectionString)
        Dim mSqlString = "UPDATE METHOD SET REC_ID=@f1,METHOD_TEMP=@f2 WHERE METHOD_TEMP=@f3"
        Dim mcommand As OleDbCommand = New OleDbCommand(mSqlString, moleDbConn)
        mcommand.CommandType = CommandType.Text
        mcommand.Parameters.AddWithValue("@f1", uid)
        mcommand.Parameters.AddWithValue("@f2", False)
        mcommand.Parameters.AddWithValue("@f3", True)
        moleDbConn.Open()
        mcommand.ExecuteNonQuery()

        'If the NewTags is empty - do not add any new tags:
        If tb_RecipeNewTags.Text = "" Then Response.Redirect("~//Profile.aspx?User=" & (HttpContext.Current.User.Identity.Name))

        'This will take the content of the textbox and break it into an array of words
        Dim newTags = tb_RecipeNewTags.Text.Split(",")

        'Go through each new tag and add it to the table of tags
        For Each newTag In newTags

            '1. Add the new tag to the tag table
            Dim newTagSql As String = "INSERT INTO Tag(Tag) VALUES(@f1)"
            Dim cmdTag As OleDbCommand = New OleDbCommand(newTagSql, oleDbConn)
            cmdTag.CommandType = CommandType.Text
            cmdTag.Parameters.AddWithValue("@f1", newTag)
            cmdTag.ExecuteNonQuery()

            '2. Find the ID of the new tag in the tag table - 
            'The last item has the largest ID number. - Therefore SELECT TOP 1 in Table ORDERBY TAG_ID DESC
            Dim getLastIdCmd As OleDbCommand = New OleDbCommand("SELECT TOP 1 TAG_ID from TAG ORDER BY TAG_ID DESC", oleDbConn)
            'using ExecuteReader since I need to get a value back
            Dim dr = getLastIdCmd.ExecuteReader()
            dr.Read()
            Dim lastId = dr.GetValue(0)

            '3. Associate this last ID with the Recipe ID - this is like the first for loop above
            Dim cmdRecipe As OleDbCommand = New OleDbCommand(SqlStringTag, oleDbConn)
            cmdRecipe.CommandType = CommandType.Text
            cmdRecipe.Parameters.AddWithValue("@f1", uid)
            'The tag foreign key ID is the ID we have just fetched
            cmdRecipe.Parameters.AddWithValue("@f2", lastId)
            cmdRecipe.ExecuteNonQuery()

        Next

        'Redirect back to the profile of the user adding the recipe
        Response.Redirect("~//Profile.aspx?User=" & (HttpContext.Current.User.Identity.Name))

    End Sub

    Protected Sub btn_AddToGallery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_AddToGallery.Click

        Dim img_Size As Integer = 0
        Dim img_width As Integer = 0
        Dim img_height As Integer = 0

        Dim myGUID As New System.Guid
        myGUID = Guid.NewGuid()
        Dim newGFileName = myGUID.ToString() + ".jpg"

        If fup_Gallery.HasFile Then
            fup_Gallery.SaveAs(Request.MapPath("Image") & "/" & newGFileName)
            Dim infoReader As System.IO.FileInfo
            infoReader = My.Computer.FileSystem.GetFileInfo(Request.MapPath("Image") & "/" & newGFileName)
            img_Size = infoReader.Length
            Dim fName As String = Request.MapPath("Image") & "/" & newGFileName
            Dim img As Image = Image.FromFile(fName)
            img_height = img.Size.Height
            img_width = img.Size.Width
            img.Dispose()
        End If

        Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("recipeConnectionString").ConnectionString)
        oleDbConn.Open()
        Dim newImageSql As String = "INSERT INTO GALLERY (GALLERY_PATH,GALLERY_SIZE,GALLERY_WIDTH,GALLERY_HEIGHT,GALLERY_TEMP) VALUES (@f1,@f2,@f3,@f4,@f5)"
        Dim cmdTag As OleDbCommand = New OleDbCommand(newImageSql, oleDbConn)
        cmdTag.CommandType = CommandType.Text
        cmdTag.Parameters.AddWithValue("@f1", "Image/" & newGFileName)
        cmdTag.Parameters.AddWithValue("@f2", img_Size)
        cmdTag.Parameters.AddWithValue("@f3", img_width)
        cmdTag.Parameters.AddWithValue("@f4", img_height)
        cmdTag.Parameters.AddWithValue("@f5", True)
        cmdTag.ExecuteNonQuery()
        cmdTag.Dispose()

        'Refresh gallery gridview
        Dim fillGallery As String = "SELECT * FROM GALLERY WHERE GALLERY_TEMP=@f1"
        Dim fillGalleryCmd As OleDbCommand = New OleDbCommand(fillGallery, oleDbConn)
        fillGalleryCmd.CommandType = CommandType.Text
        fillGalleryCmd.Parameters.AddWithValue("@f1", True)
        Dim galDataReader = fillGalleryCmd.ExecuteReader()
        grd_Gallery.DataSource = galDataReader
        grd_Gallery.DataBind()
        oleDbConn.Close()
        oleDbConn.Dispose()
    End Sub

    Protected Sub btn_AddToIngredients_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_AddToIngredients.Click

        Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("recipeConnectionString").ConnectionString)
        oleDbConn.Open()
        Dim newIngSql As String = "INSERT INTO INGREDIENT (ING_QTY,ING_DESC,ING_TEMP) VALUES (@f1,@f2,@f3)"
        Dim cmdTag As OleDbCommand = New OleDbCommand(newIngSql, oleDbConn)
        cmdTag.CommandType = CommandType.Text
        cmdTag.Parameters.AddWithValue("@f1", ing_Qty.Text)
        cmdTag.Parameters.AddWithValue("@f2", ing_Desc.Text)
        cmdTag.Parameters.AddWithValue("@f3", True)
        cmdTag.ExecuteNonQuery()
        cmdTag.Dispose()
        ing_Qty.Text = ""
        ing_Desc.Text = ""

        'Refresh ingredients gridview
        Dim fillIngredients As String = "SELECT * FROM INGREDIENT WHERE ING_TEMP=@f1"
        Dim fillIngredientsCmd As OleDbCommand = New OleDbCommand(fillIngredients, oleDbConn)
        fillIngredientsCmd.CommandType = CommandType.Text
        fillIngredientsCmd.Parameters.AddWithValue("@f1", True)
        Dim ingDataReader = fillIngredientsCmd.ExecuteReader()
        grd_Ingredients.DataSource = ingDataReader
        grd_Ingredients.DataBind()
        oleDbConn.Close()
        oleDbConn.Dispose()

    End Sub

    Protected Sub btn_AddToMethods_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_AddToMethods.Click

        Dim newMethOleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("recipeConnectionString").ConnectionString)
        newMethOleDbConn.Open()
        Dim newMethSql As String = "INSERT INTO METHOD (METHOD_DESC,METHOD_TEMP) VALUES (@f1,@f2)"
        Dim newMethCmd As OleDbCommand = New OleDbCommand(newMethSql, newMethOleDbConn)
        newMethCmd.CommandType = CommandType.Text
        newMethCmd.Parameters.AddWithValue("@f1", meth_Desc.Text)
        newMethCmd.Parameters.AddWithValue("@f2", True)
        newMethCmd.ExecuteNonQuery()
        newMethCmd.Dispose()
        meth_Desc.Text = ""

        'Refresh methods gridview
        Dim fillMethods As String = "SELECT * FROM METHOD WHERE METHOD_TEMP=@f1"
        Dim fillMethodsCmd As OleDbCommand = New OleDbCommand(fillMethods, newMethOleDbConn)
        fillMethodsCmd.CommandType = CommandType.Text
        fillMethodsCmd.Parameters.AddWithValue("@f1", True)
        Dim methDataReader = fillMethodsCmd.ExecuteReader()
        grd_Method.DataSource = methDataReader
        grd_Method.DataBind()
        newMethOleDbConn.Close()
        newMethOleDbConn.Dispose()

    End Sub

    Sub grd_Gallery_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)

    End Sub

End Class